﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace BanHangCshap.Class
{
    class BoSanPham:Database
    {
        #region ***** Fields & Properties *****
        private string _mabosp;
        public string mabosp
        {
            get
            {
                return _mabosp;
            }
            set
            {
                _mabosp = value;
            }
        }
        private string _tenbosp;
        public string tenbosp
        {
            get
            {
                return _tenbosp;
            }
            set
            {
                _tenbosp = value;
            }
        }
        private string _dvt;
        public string dvt
        {
            get
            {
                return _dvt;
            }
            set
            {
                _dvt = value;
            }
        }

        private string _ghichu;
        public string ghichu
        {
            get
            {
                return _ghichu;
            }
            set
            {
                _ghichu = value;
            }
        }
        private double _gianhap;
        public double gianhap
        {
            get
            {
                return _gianhap;
            }
            set
            {
                _gianhap = value;
            }
        }
        private double _giaxuatsi;
        public double giaxuatsi
        {
            get
            {
                return _giaxuatsi;
            }
            set
            {
                _giaxuatsi = value;
            }
        }
        private double _giaxuatle;
        public double giaxuatle
        {
            get
            {
                return _giaxuatle;
            }
            set
            {
                _giaxuatle = value;
            }
        }
        private string _maloai;
        public string maloai
        {
            get
            {
                return _maloai;
            }
            set
            {
                _maloai = value;
            }
        }
        #endregion
        public BoSanPham() { }
        public BoSanPham(string mabosp) {
            string sql = "select * from BoSanPham where mabosp = '" + mabosp + "'";
            foreach (DataRow dr in getDataTable(sql).Rows)
            {
                this.mabosp = dr["mabosp"].ToString().Trim();
                //this.tenbosp = dr["tenbosp"].ToString().Trim();
                
                this.gianhap = double.Parse(dr["gianhap"].ToString());
                this.giaxuatsi = double.Parse(dr["giaxuatsi"].ToString());
                this.giaxuatle = double.Parse(dr["giaxuatle"].ToString());
                

            }
        }
        public DataTable layDSBoSanPham()
        {
            string sql = "select convert(bit,'false') as chon,mabosp, tenhang as tenbosp,  a.gianhap, a.giaxuatsi, a.giaxuatle from BoSanPham a left join HangHoa b on a.mabosp = b.mahang ";
            return getDataTable(sql);
        }
        
        public Boolean themBoSanPham()
        {
            string[] field = { "mabosp",  "gianhap", "giaxuatsi", "giaxuatle" };
            Object[] value = { (object)mabosp,   (object)gianhap, (object)giaxuatsi, (object)giaxuatle };
            return addTrans("BoSanPham", field, value);
        }
        public Boolean xoaBoSanPham(string mabosp)
        {
            string sql = "delete BoSanPham where mabosp = '" + mabosp + "'";
            return executeQueryTrans(sql);
        }
        public DataTable layDSBoSanPhamCT(string mabosp)
        {
            string sql = "select * from BoSanPhamCT where mabosp = '" + mabosp + "'";
            return getDataTable(sql);
        }
        public DataTable inPhieuXuatHang(string tendoanhnghiep, string diachi, string sodt,string manhapxuat)
        {
            string sql = "select distinct stt,case when loai = 1 then N'PHIẾU NHẬP HÀNG' ";
            sql += "when loai = 2 then N'PHIẾU TRẢ HÀNG' ";
            sql += "WHEN loai = 3 then N'PHIẾN TRẢ HÀNG' ";
            sql += "WHEN loai = 4 then N'PHIẾU XUẤT HÀNG' ";
            sql += "WHEN loai = 5 then N'PHIẾU XUẤT HÀNG' END AS tenreport ";
            sql += ",N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi, '" + sodt + "' as sodt,a.manhapxuat,N'Ngày ' + case when len(convert(char(2),day(convert(datetime,ngaynhapxuat,103)))) = 1 then '0' + convert(char(2),day(convert(datetime,ngaynhapxuat,103))) else convert(char(2),day(convert(datetime,ngaynhapxuat,103))) end + N' tháng ' + case when len(convert(char(2),month(convert(datetime,ngaynhapxuat,103)))) = 1 then '0' + convert(char(2),month(convert(datetime,ngaynhapxuat,103))) else convert(char(2),month(convert(datetime,ngaynhapxuat,103))) end  + N' năm ' + convert(char(4),year(ngaynhapxuat)) as ngaynhapxuat,tenkhncc,diachikhncc,sodtkhncc,ghichunx,tenbosp as tenhang,b.dvt,dongia as dgnhapxuat,chuthich,convert(varchar(5),soluong) as slchuaquydoi,b.thanhtien, b.chietkhau, tongtienso,tongtienchu,thanhtoan,nocu,tongnoso ";
            sql += "from v_PhieuNhapXuat a, BoSanPhamNhapXuat b , BoSanPham c ";
            sql += "where a.manhapxuat = '" + manhapxuat + "' and  a.manhapxuat = b.manhapxuat and b.mabosp = c.mabosp order by stt ";
            return getDataTable(sql);
        }
        public DataTable inDSBoSanPham(string tendoanhnghiep, string diachi, string sodt)
        {
            string sql = "select distinct N'" + tendoanhnghiep + "' as tendoanhnghiep, N'" + diachi + "' as diachi, '" + sodt + "' as sodt,mabosp,tenbosp,dvt, gianhap, giaxuatsi, giaxuatle, ";
            sql += "nl_mahang ,nl_tenhang,nl_dvt,convert(varchar(8),nl_soluong) as nl_soluong,nl_dongia,nl_thanhtien,id from v_DSBoSanPham order by tenbosp,id ";
            return getDataTable(sql);
        }
        public SqlDataAdapter layDSBoSanPham1()
        {
            string sql = "select a.mabosp,tenhang as tenbosp,dvt, a.gianhap, a.giaxuatsi, a.giaxuatle from BoSanPham a left join HangHoa b on a.mabosp = b.mahang";
            SqlDataAdapter adap = new SqlDataAdapter(sql, DatabaseStatic.conn);
            return adap;
        }
        public SqlDataAdapter layDSBoSanPhamCT1()
        {
            string sql = "select distinct mabosp,a.mahang,tenhang,a.dvt,soluong,dongia,thanhtien,id from BoSanPhamCT a left join HangHoa b on a.mahang = b.mahang order by id";
            SqlDataAdapter adap = new SqlDataAdapter(sql, DatabaseStatic.conn);
            return adap;
        }
        public SqlDataAdapter layDSBoSanPhamCT2()
        {
            string sql = "select distinct mabosp,a.mahang,tenhang,a.dvt,dongia,vanchuyen,nuocson,pvc, keo,cong,thanhtien,id from BoSanPhamThiCong a left join HangHoa b on a.mahang = b.mahang order by id";
            SqlDataAdapter adap = new SqlDataAdapter(sql, DatabaseStatic.conn);
            return adap;
        }
        public Boolean isExistBoSanPham(string mabosp)
        {
            string sql = "select mabosp from BoSanPham where mabosp = '" + mabosp + "'";
            return isExist(sql);
        }
    }
}
